package com.token.sql;
import java.sql.*;

import com.token.base.DBEnum;
import com.token.base.FuncUtil;

import lotus.domino.Database;
import lotus.domino.Document;
import lotus.domino.View;
/**
 * 关系型数据库处理配置、连接池、执行SQL语句等。
 * @author chenjianxiong  2020/2/16
 */
public class IntegrateSql{
    protected Connection con;
    private PreparedStatement pst;
    private Database initDb = null;
    private String configName = "ToKen";
    protected boolean isInitCon = false;
    private FuncUtil funcUtil;
    /**
     * 构造函数
     * @param f 操作类库
     * @param conAutoCommit 手动提交事务（false）
     * @param isInit true 连接sql数据库
     * @throws Exception 错误抛出
     */
    public IntegrateSql(FuncUtil f,boolean isInit,boolean conAutoCommit) throws Exception {
        funcUtil = f;
        initPoolCon(isInit,conAutoCommit);
    }
    /**
     * 构造函数
     * @param f 操作类库
     * @param isInit true 连接sql数据库
     * @param conAutoCommit 手动提交事务（false）
     * @param configName 配置名称
     * @throws Exception 错误抛出
     */
    public IntegrateSql(FuncUtil f,boolean isInit,boolean conAutoCommit,String configName) throws Exception {
        funcUtil = f;
        this.configName = configName;
        initPoolCon(isInit,conAutoCommit);
    }

    public IntegrateSql(FuncUtil f) throws Exception {
        funcUtil = f;
    }

    /**
     * 直联
     * @throws Exception 抛出错误
     */
    public void initCon() throws Exception{
        if(initDb == null){
            initDb = funcUtil.openDb(DBEnum.SQLDB.getDbName());
        }
        if(initDb == null) {
            throw new Exception("无法打开数据库["+ DBEnum.SQLDB.getDbName() +"]");
        }

        View view = initDb.getView("vw_fmLinkList_ConnectionName");
        view.setAutoUpdate(false);
        Document doc = view.getDocumentByKey(this.configName, true);
        if(doc != null){
            String driverName = doc.getItemValueString("driverClass");
            String dbURL = doc.getItemValueString("url");
            String userName = doc.getItemValueString("user");
            String userPwd = doc.getItemValueString("password");
            Class.forName(driverName);
            con = DriverManager.getConnection(dbURL, userName, userPwd);
            isInitCon = true;
            doc.recycle();
        }else{
            throw new Exception("没有找到配置：" + configName);
        }

        view.recycle();
    }
    /**
     * 通过连接池连接sql
     * @param isInit true 连接sql数据库
     * @param conAutoCommit 手动提交事务（false）
     * @throws Exception 错误抛出
     */
    public void initPoolCon(boolean isInit,boolean conAutoCommit) throws Exception{
        if(isInit){
            Pool pool = new Pool();
            if(initDb == null){
                initDb = funcUtil.openDb(DBEnum.SQLDB.getDbName());
            }
            if(initDb == null) {
                throw new Exception("无法打开数据库["+ DBEnum.SQLDB.getDbName() +"]");
            }
            StringBuilder builder = new StringBuilder(50);
            builder.append(funcUtil.getCurPath().toUpperCase()).append("_").append(configName).append("Pool");
            con = pool.getConnectionByPoolName(initDb,builder.toString());
            this.setAutoCommit(conAutoCommit);
            isInitCon = true;
        }
    }

    /**
     * 获取数据库类型
     * @return String
     */
    public String getDbType(){
        String dbType = null;
        try{
            if(initDb == null){
                initDb = funcUtil.openDb(DBEnum.SQLDB.getDbName());
            }
            View view = initDb.getView("vw_fmLinkList_ConnectionName");
            Document appDoc = view.getDocumentByKey(configName, true);
            dbType = appDoc.getItemValueString("DBType").toLowerCase();
            appDoc.recycle();
            view.recycle();
        }catch(Exception e){
            e.printStackTrace();
        }
        return dbType;
    }

    /**
     * 设置提交事务
     * @param conAutoCommit 手动提交事务(false) 自动提交事务(true)
     * @throws SQLException 错误抛出
     */
    public void setAutoCommit(boolean conAutoCommit) throws SQLException{
        con.setAutoCommit(conAutoCommit);
    }

    /**
     * 关闭连接。
     * @throws SQLException 错误抛出
     */
    public void closeConn() throws SQLException{
        if (pst != null ) {pst.close();}
        if(con != null) {con.close();}
        isInitCon = false;
    }

    /**
     * 事务提交
     * @throws SQLException 错误抛出
     */
    public void commit() throws SQLException{
        if(isInitCon){
            if(!con.getAutoCommit()) {con.commit();}
        }
    }

    /**
     * 事务回滚
     */
    public void rollback(){
        try {
            if(isInitCon){
                if(!con.getAutoCommit()) {con.rollback();}
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 删除数据
     * @param tableName 表名
     * @param key 条件字段
     * @param keyValue 条件值
     * @return boolean
     * @throws SQLException 错误抛出
     */
    public int deleteData(String tableName, String key, String keyValue) throws SQLException {
        StringBuilder sql = new StringBuilder(50);
        sql.append("delete from ")
                .append(tableName)
                .append(" where ")
                .append(key).append("='")
                .append(keyValue)
                .append("'");
        return this.executeUpdate(sql.toString());
    }

    /**
     * 执行sql
     * @param sql SQL
     * @return boolean
     * @throws SQLException 错误抛出
     */
    public boolean execute(String sql) throws SQLException{
        pst = con.prepareStatement(sql);
        return pst.execute();
    }

    /**
     * @throws SQLException 错误抛出
     *
     */
    public void closePst() throws SQLException{
        if(this.pst != null) {this.pst.close();}
    }

    /**
     * 静态SQL模式，获取新增记录ID
     * @param sql SQL
     * @return Long 主键
     * @throws SQLException 错误抛出
     */
    public long executeUpdateReturnKey(String sql) throws SQLException{
        long id = 0;
        pst = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        pst.executeUpdate();
        //检索由于执行此 Statement 对象而创建的所有自动生成的键
        ResultSet rs = pst.getGeneratedKeys();
        if (rs.next()) {
            id = rs.getLong(1);
        }
        rs.close();
        pst.close();
        return id;
    }
    /**
     * 获取保存后自动生成的键
     * 需setInsertFieldsReturn 函数使用
     * @param pst 预编译对象
     * @return long 主键
     * @throws SQLException 错误抛出
     */
    public long getGeneratedKeys(PreparedStatement pst) throws SQLException{
        long id = 0;
        ResultSet rs = pst.getGeneratedKeys();
        if (rs.next()) {
            id= rs.getLong(1);
        }
        rs.close();
        return id;
    }

    /**
     *
     * @return Long 主键
     * @throws SQLException 错误抛出
     */
    public long getGeneratedKeys() throws SQLException{
        return this.getGeneratedKeys(pst);
    }
    /**
     * 执行sql语句，适用于insert into,update,delete 等语句
     * @param sql SQL
     * @return int
     * @throws SQLException 错误抛出
     */
    public int executeUpdate(String sql) throws SQLException {
        pst = con.prepareStatement(sql);
        return pst.executeUpdate();
    }


    /**
     * 执行select SQL语句 返回结果集
     * @param sql SQL
     * @return ResultSet rs
     * @throws SQLException 错误抛出
     */
    public ResultSet executeQuery(String sql) throws SQLException {
        pst = this.con.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        return pst.executeQuery();
    }

    /**
     * 执行select SQL语句 返回结果集
     * @param sql SQL
     * @param searchkeys 条件值
     * @return ResultSet
     * @throws SQLException 错误抛出
     */
    public ResultSet executeQuery(String sql,Object searchkeys) throws SQLException{
        pst = this.con.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        pst.setObject(1, searchkeys);
        return pst.executeQuery();
    }

    /**
     * 换取连接
     * @return Connection
     */
    public Connection getCon() {
        return con;
    }

    /**
     * 获取预处理
     * @return PreparedStatement
     */
    public PreparedStatement getPst() {
        return pst;
    }
    /**
     * 关闭数据库链接
     */
    public void recycle() {
        try {
            if(pst != null ) {pst.close();}
            if(con != null) {con.close();}
            if(initDb != null) {initDb.recycle();}
            isInitCon = false;
        }catch(Exception e){
            e.printStackTrace();
        }
    }


    /**
     * 判断是否已经初始化数据库连接
     * @return true 是  false 否
     */
    public boolean isInitCon() {
        return isInitCon;
    }


    /**
     * 执行指定的SQL语句，并返回第一行记录字段的值。
     * @param sql sql
     * @param fieldName 取值字段
     * @return String
     * @throws SQLException 错误抛出
     */
    public String selectData(String sql, String fieldName) throws SQLException{
        String temp;
        Statement stmt = this.con.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        if(rs.next()){
            temp = rs.getString(fieldName);
        }else{
            temp = "";
        }
        rs.close();
        stmt.close();
        return temp;
    }

    /**
     * 返回结果集条数
     * @param tableName 表名
     * @param fieldName 搜索字段
     * @param key 搜索值
     * @return 返回结果集条数
     */
    public long selectCount(String tableName,String fieldName,String key) {
        StringBuilder selectStr = new StringBuilder(50);
        selectStr.append("select count(*) from ")
            .append(tableName)
            .append(" where ")
                .append(fieldName)
                .append("=?");
        long count = 0;
        ResultSet rs;
        try {
            pst = this.con.prepareStatement(selectStr.toString());
            pst.setString(1, key);
            rs = pst.executeQuery();
            if (rs.next()) {
                count = rs.getLong(1);
            }
            rs.close();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return count;
    }

    /**
     * 获取表的总记录
     * @param tableName 表名
     * @param where where 条件
     * @return 数量
     */
    public long selectCount(String tableName,String where) {
        StringBuilder selectStr = new StringBuilder(50);
        selectStr.append("select count(*) from ").append(tableName);
        if(!"".equals(where)){
            selectStr.append(" where ").append(where);
        }
        long count = 0;
        ResultSet rs;
        try {
            Statement stmt = this.con.createStatement();
            rs = stmt.executeQuery(selectStr.toString());
            if (rs.next()) {
                count = rs.getLong(1);

            }
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return count;
    }

    /**
     * 返回类库
     * @return FuncUtil
     */
    public FuncUtil getFuncUtil() {
        return funcUtil;
    }
}
